package com.drool.mediaplayer.Database;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.MediaStore.Audio;
import android.provider.MediaStore.Video;
import android.util.Log;

import com.drool.mediaplayer.Database.MediaFilesDB.Playlist;
import com.drool.mediaplayer.Database.MediaFilesDB.PlaylistItems;
import com.drool.mediaplayer.Extracter.AudioInfoExtracter;
import com.drool.mediaplayer.Extracter.VideoInfoExtracter;

public class WritableDatabase {
	
	private MediaFilesDBHelper myDBHelper;
	private String TAG = "Drool:WritableDatabase";
	public static final String AUDIO = "Audio";
	public static final String VIDEO = "Video";
	public static final String PLAYLIST_TYPE_NEW = "new";
	public static final String PLAYLIST_TYPE_CURRENT = "current";
	 
	private Context context;
	
	public WritableDatabase(Context con){
		context = con;
		myDBHelper = new MediaFilesDBHelper(con);
		
	}	
	
	public boolean updateNumTimesPlayed(String mediaType, String mediaDisplayName){
		
		if (mediaType == AUDIO){
			SQLiteDatabase dbWritable = myDBHelper.getWritableDatabase();
			Cursor c;
			
			Log.i(TAG, mediaDisplayName);
			
			c = dbWritable.query(MediaFilesDB.FileHeader.HDR_TABLE_NAME + ","
					+ MediaFilesDB.AudioFiles.AUDIO_TABLE_NAME,
					new String[] {MediaFilesDB.AudioFiles.AUDIO_TABLE_NAME + "."+ MediaFilesDB.AudioFiles._ID,
								MediaFilesDB.AudioFiles.AUDIO_NUM_OF_TIMES} ,
					MediaFilesDB.FileHeader.HDR_DISP_NAME + " = ?"
					+ " and " + MediaFilesDB.FileHeader.HDR_TABLE_NAME + "." + MediaFilesDB.FileHeader.HDR_FILE_ID
					+ "=" + MediaFilesDB.AudioFiles.AUDIO_TABLE_NAME + "." + MediaFilesDB.AudioFiles._ID
					+ " and " + MediaFilesDB.FileHeader.HDR_FILE_TYPE + "=\'a\'",
					new String[] {mediaDisplayName}, null, null, null);
			
			((Activity) context).startManagingCursor(c);
			
			c.moveToFirst();
			
			Log.i(TAG, Integer.toString(c.getCount()) + " song id " + c.getString(0));
			
			if (c.getCount()> 0){
				int numOfTimes = c.getInt(1);
				ContentValues values = new ContentValues();
				values.put(MediaFilesDB.AudioFiles.AUDIO_NUM_OF_TIMES, numOfTimes + 1);				
				dbWritable.beginTransaction();
				try{
					dbWritable.update(MediaFilesDB.AudioFiles.AUDIO_TABLE_NAME, values, 
						MediaFilesDB.AudioFiles._ID + " =?", 
						new String []{c.getString(0)});
					dbWritable.setTransactionSuccessful();
				}catch(Exception e){
					Log.e(TAG, "DB Update failed");
					//TODO code rollback - three inserts have to be rollbacked potentially
					return false;
				}finally{
					dbWritable.endTransaction();
				}
			}
			else{
				//TODO handle missing song in library
			}
				
			c.close();
			dbWritable.close();			
		}
		
		if (mediaType == VIDEO){
			SQLiteDatabase dbWritable = myDBHelper.getWritableDatabase();
			Cursor c;
						
			Log.i(TAG, mediaDisplayName);
			
			c = dbWritable.query(MediaFilesDB.FileHeader.HDR_TABLE_NAME + ","
					+ MediaFilesDB.VideoFiles.VIDEO_TABLE_NAME,
					new String[] {MediaFilesDB.VideoFiles.VIDEO_TABLE_NAME + "." + MediaFilesDB.VideoFiles._ID,
								MediaFilesDB.VideoFiles.VIDEO_NUM_OF_TIMES} ,
					MediaFilesDB.FileHeader.HDR_DISP_NAME + " = ?"
					+ " and " + MediaFilesDB.FileHeader.HDR_TABLE_NAME + "." + MediaFilesDB.FileHeader.HDR_FILE_ID
					+ "=" + MediaFilesDB.VideoFiles.VIDEO_TABLE_NAME + "." + MediaFilesDB.VideoFiles._ID
					+ " and " + MediaFilesDB.FileHeader.HDR_FILE_TYPE + "=\'v\'",
					new String[] {mediaDisplayName}, null, null, null);
			((Activity) context).startManagingCursor(c);
			c.moveToFirst();
			
			
			if (c.getCount()> 0){
				Log.i(TAG, Integer.toString(c.getCount()) + " vidoe id " + c.getString(0));
				int numOfTimes = c.getInt(1);
				ContentValues values = new ContentValues();
				values.put(MediaFilesDB.VideoFiles.VIDEO_NUM_OF_TIMES, numOfTimes + 1);				
				dbWritable.beginTransaction();
				try{
					dbWritable.update(MediaFilesDB.VideoFiles.VIDEO_TABLE_NAME, values, 
						MediaFilesDB.VideoFiles._ID+ " =?", 
						new String []{c.getString(0)});
					dbWritable.setTransactionSuccessful();
				}catch(Exception e){
					Log.e(TAG, "DB Update failed");
					//TODO code rollback - three inserts have to be rollbacked potentially
					return false;
				}finally{
					dbWritable.endTransaction();
				}
			}
			else{
				//TODO handle missing song in library
			}
				
			c.close();
			dbWritable.close();			
		}
		
		return true;
	}
	
	public boolean updateAudioLib(Activity act) 
	{ 
		SQLiteDatabase dbWritable = myDBHelper.getWritableDatabase();
		Cursor HeaderCur = null; 
		int recs_updated = 0;
		
		AudioInfoExtracter a = new AudioInfoExtracter();
		
		a.mCursorAudio = act.managedQuery(a.mMedia, a.projection, null, null,Audio.Media.DATE_ADDED + " ASC");
		act.startManagingCursor(a.mCursorAudio);
		
		Log.i(TAG, "Executing UPDATE AUDIO LIB " + Integer.toString(a.mCursorAudio.getCount()));
		a.mCursorAudio.moveToFirst();
		
		while (a.mCursorAudio.isAfterLast() == false){
			Log.i(TAG, "Inside While");
			HeaderCur = dbWritable.query(MediaFilesDB.FileHeader.HDR_TABLE_NAME,new String[] {MediaFilesDB.FileHeader.HDR_FILE_NAME} ,
					MediaFilesDB.FileHeader.HDR_FILE_NAME + " = ?", 
					new String[] {a.mCursorAudio.getString(1)}, null, null, null);
			((Activity) context).startManagingCursor(HeaderCur);
			
			if (HeaderCur.getCount()==0){
				dbWritable.beginTransaction();
				try{
					ContentValues values = new ContentValues();
					values.put(MediaFilesDB.FileHeader.HDR_FILE_NAME, a.mCursorAudio.getString(1));	
					values.put(MediaFilesDB.FileHeader.HDR_DISP_NAME, a.mCursorAudio.getString(2));
					values.put(MediaFilesDB.FileHeader.HDR_FILE_TYPE, "a");
					
					Calendar calendar = Calendar.getInstance();
					SimpleDateFormat dateFormat = new SimpleDateFormat ("ddMMyyyyHHmmssSS");				
					
					values.put(MediaFilesDB.FileHeader.HDR_DATE_ADDED,dateFormat.format(calendar.getTime()) );
					
					dbWritable.insert(MediaFilesDB.FileHeader.HDR_TABLE_NAME, null, values);
					
					values.clear();
					//values.put(MediaFilesDB.AudioFiles.AUDIO_ALBUM, a.mCursorAudio.getString(3));
					values.put(MediaFilesDB.AudioFiles.AUDIO_ALBUM_ID, a.mCursorAudio.getString(3));
					values.put(MediaFilesDB.AudioFiles.AUDIO_ARTIST, a.mCursorAudio.getString(4));
					values.put(MediaFilesDB.AudioFiles.AUDIO_DURATION, a.mCursorAudio.getString(5));
					values.put(MediaFilesDB.AudioFiles.AUDIO_TRACK, a.mCursorAudio.getString(6));
					values.put(MediaFilesDB.AudioFiles.AUDIO_YEAR, a.mCursorAudio.getString(7));
					values.put(MediaFilesDB.AudioFiles.AUDIO_NUM_OF_TIMES, 0);
					//values.put(MediaFilesDB.AudioFiles.AUDIO_ART, "");
					long id = dbWritable.insert(MediaFilesDB.AudioFiles.AUDIO_TABLE_NAME, null, values);
					values.clear();
					values.put(MediaFilesDB.FileHeader.HDR_FILE_ID, id);
					dbWritable.update(MediaFilesDB.FileHeader.HDR_TABLE_NAME, values, MediaFilesDB.FileHeader.HDR_FILE_NAME + " =?", new String []{a.mCursorAudio.getString(1)});
					dbWritable.setTransactionSuccessful();
					recs_updated = recs_updated+1;
				}catch(Exception e){
					Log.e(TAG, "DB Update failed");
					//TODO code rollback - three inserts have to be rollbacked potentially
					continue;
				}finally{
					dbWritable.endTransaction();
				}
			}
			else{
							
			}
			
			a.mCursorAudio.moveToNext();
		}
		Log.i(TAG, "UPDATE Ends - " + Integer.toString(recs_updated));
		a.mCursorAudio.close();
		if (HeaderCur != null)
			HeaderCur.close();
		dbWritable.close();
		return true;
		
	}
	
	
	public boolean updateVideoLib(Activity act)
	{
		SQLiteDatabase dbWritable = myDBHelper.getWritableDatabase();		
		Cursor HeaderCur = null;
		int recs_updated = 0;
		
		VideoInfoExtracter v = new VideoInfoExtracter();
		
		v.mCursorVideo = act.managedQuery(v.mMedia, v.projection, null, null,Video.Media.DATE_ADDED + " ASC");
		act.startManagingCursor(v.mCursorVideo);
		
		Log.i(TAG, "Executing UPDATE AUDIO LIB " + Integer.toString(v.mCursorVideo.getCount()));
		
		
		v.mCursorVideo.moveToFirst();
		
		while (v.mCursorVideo.isAfterLast() == false){
			HeaderCur = dbWritable.query(MediaFilesDB.FileHeader.HDR_TABLE_NAME,new String[] {MediaFilesDB.FileHeader.HDR_FILE_NAME} ,
					MediaFilesDB.FileHeader.HDR_FILE_NAME + " = ?", 
					new String[] {v.mCursorVideo.getString(1)}, null, null, null);
			((Activity) context).startManagingCursor(HeaderCur);
			
			if (HeaderCur.getCount()==0){
				dbWritable.beginTransaction();
				try{
					ContentValues values = new ContentValues();
					values.put(MediaFilesDB.FileHeader.HDR_FILE_NAME, v.mCursorVideo.getString(1));	
					values.put(MediaFilesDB.FileHeader.HDR_DISP_NAME, v.mCursorVideo.getString(2));
					values.put(MediaFilesDB.FileHeader.HDR_FILE_TYPE, "v");
					
					Calendar calendar = Calendar.getInstance();
					SimpleDateFormat dateFormat = new SimpleDateFormat ("ddMMyyyyHHmmssSS");				
					
					values.put(MediaFilesDB.FileHeader.HDR_DATE_ADDED,dateFormat.format(calendar.getTime()) );
					
					dbWritable.insert(MediaFilesDB.FileHeader.HDR_TABLE_NAME, null, values);
					
					values.clear();
					
					values.put(MediaFilesDB.VideoFiles.VIDEO_ALBUM, v.mCursorVideo.getString(3));
					values.put(MediaFilesDB.VideoFiles.VIDEO_ARTIST, v.mCursorVideo.getString(4));
					values.put(MediaFilesDB.VideoFiles.VIDEO_DURATION, v.mCursorVideo.getString(5));
					values.put(MediaFilesDB.VideoFiles.VIDEO_DESCRIPTION, v.mCursorVideo.getString(6));
					values.put(MediaFilesDB.VideoFiles.VIDEO_LANGUAGE,v.mCursorVideo.getString(7));
					values.put(MediaFilesDB.VideoFiles.VIDEO_NUM_OF_TIMES,0);
					
					long id = dbWritable.insert(MediaFilesDB.VideoFiles.VIDEO_TABLE_NAME, null, values);
					values.clear();
					
					values.put(MediaFilesDB.FileHeader.HDR_FILE_ID, id);
					dbWritable.update(MediaFilesDB.FileHeader.HDR_TABLE_NAME, values, MediaFilesDB.FileHeader.HDR_FILE_NAME + " =?", new String []{v.mCursorVideo.getString(1)});
					dbWritable.setTransactionSuccessful();
					recs_updated = recs_updated+1;
				}catch(Exception e){
					Log.e(TAG, "DB Update failed");
					//TODO code rollback - three inserts have to be rollbacked potentially
					continue;
				}finally{
					dbWritable.endTransaction();
				}
			}
			else{
							
			}
			
			v.mCursorVideo.moveToNext();
		}
		Log.i(TAG, "UPDATE Ends - " + Integer.toString(recs_updated));
		v.mCursorVideo.close();
		if (HeaderCur != null)
			HeaderCur.close();
		dbWritable.close();
		return true;
	}
}